package com.jdbc;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.junit.Test;

import com.bean.Stu;
import com.tools.JdbcUtils;

public class TestDbutils {
	
	private QueryRunner qr = new QueryRunner(JdbcUtils.getDataSource());
	
	@Test
	public void test1() throws SQLException {
		String sql = "insert into stu(name, gender, age) value (?,?,?)";
		qr.update(sql, "张三","男","15");
		//String sql = "update stu set age = ?";
		//qr.update(sql, "99");
		//String sql = "delete from stu";
		//qr.update(sql);
	}
	
	/**
	 * 测试BeanHandler
	 * @throws SQLException
	 */
	@Test
	public void test2() throws SQLException {
		String sql = "select * from stu where id = ?";
		Stu stu = (Stu)qr.query(sql, new BeanHandler<Stu>(Stu.class), 2);
		System.out.println(stu);
	}
	
	/**
	 * 测试BeanListHandler
	 * @throws SQLException
	 */
	@Test
	public void test3() throws SQLException {
		String sql = "select * from stu";
		List<Stu> stuList = new ArrayList<Stu>();
		stuList = qr.query(sql, new BeanListHandler<Stu>(Stu.class));
		System.out.println(stuList);
	}
	
	/**
	 * 测试MapHandler
	 * @throws SQLException
	 */
	@Test
	public void test4() throws SQLException {
		String sql = "select * from stu where id = ?";
		Map map = new HashMap();
		Object[] params = {2};
		map = qr.query(sql, new MapHandler(), params);
		System.out.println(map);
	}
	
	/**
	 * 测试MapListHandler
	 * @throws SQLException
	 */
	@Test
	public void test5() throws SQLException {
		String sql = "select * from stu";
		List<Map<String, Object>> list = qr.query(sql, new MapListHandler());
		System.out.println(list);
	}
	
	/**
	 * 测试ScalarHandler
	 * @throws SQLException
	 */
	@SuppressWarnings("unchecked")
	@Test
	public void test6() throws SQLException {
		String sql = "select count(*) from stu";
		/*
		 * 该方法的返回值会是Long,Integer,BigInteger
		 */
		//Object obj = qr.query(sql, new ScalarHandler<Stu>());
		//最安全的写法
		//返回值用一个Number类型的变量接收，需要用什么类型的值就转换成什么类型
		Number num = (Number)qr.query(sql, new ScalarHandler());
		int num1 = num.intValue();
		System.out.println(num1);
	}
	
}
